﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using DaZhong;

namespace QD.Sht.Common
{
    /// <summary>
    /// SQL Server操作类
    /// 2013-07-10 wanghui
    /// </summary>
    public sealed class SqlHelper2013
    {
        private SqlHelper2013() { }
        //public static readonly string CONN_STRING = "Data Source=X6X8-20140218ZT;User ID=sa;Password=123456;Initial Catalog=sql_weijie;Pooling=true";
        //public static readonly string CONN_STRING = "Data Source=114.55.133.202,2434;User ID=yin;Password=yin1016;Initial Catalog=sql_s003;Pooling=true";
        public static readonly string CONN_STRING = "Data Source=112.124.17.98,2434;User ID=wh;Password=wanghui2006;Initial Catalog=sql_s003;Pooling=true";

        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                using (SqlConnection conn = new SqlConnection(CONN_STRING))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return rdr;
                }
            }
            catch (Exception ex)
            {
                Log.WriteErr(string.Format("数据库错误: {0},{1}", cmdText, ex.ToString()));
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            return null;
        }

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            object val = "";
            SqlCommand cmd = new SqlCommand();
            try
            {
                using (SqlConnection conn = new SqlConnection(CONN_STRING))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

                    val = cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                Log.WriteErr(string.Format("ExecuteScalar插入数据库错误: {0},{1}", cmdText, ex.ToString()));
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            return val; 
        }

        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            object val = "";
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            try
            {
                val = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            return val;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = cmdText;

                if (trans != null)
                    cmd.Transaction = trans;

                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }
            catch (Exception ex)
            {
                Log.WriteErr(string.Format("PrepareCommand插入数据库错误: {0},{1}", cmdText, ex.ToString()));
            }
           
        }

        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, cmdParms);
        }

        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            int count = 0;
            SqlCommand cmd = new SqlCommand();
            try
            {
                using (SqlConnection conn = new SqlConnection(CONN_STRING))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

                    count = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Log.WriteErr(string.Format("ExecuteNonQuery插入数据库错误: {0},{1}", cmdText, ex.ToString()));
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            return count;
            
        }

        public static DataTable ExecuteReSult(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            try
            {
                using (SqlConnection conn = new SqlConnection(CONN_STRING))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        PrepareCommand(adapter.SelectCommand, conn, null, cmdType, cmdText, cmdParms); 
                        adapter.Fill(dt); 
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteErr(string.Format("ExecuteReSult插入数据库错误: {0},{1}", cmdText, ex.ToString()));
            }
            finally
            {
                //adapter.SelectCommand.Parameters.Clear();
                cmd.Dispose();
            } 
            return dt;
        }
    }
}
